5.6 Paginate Pagination

1. Common paginate Usage

Both the Model and Db provide the most commonly used pagination API: paginate(int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras).

The parameters respectively represent: the current page number, number of data items per page, the 'select' part of the SQL, the part of the SQL excluding 'select', and the query parameters. In most cases, this API suffices. Here's an example:

dao.paginate(1, 10, "select *", "from girl where age > ? and weight < ?", 18, 50);

2. paginate with sql having an outermost group by

API prototype: paginate(int pageNumber, int pageSize, boolean isGroupBySql, String select, String sqlExceptSelect, Object... paras). Compared to the first type, this one just has an extra boolean isGroupBySql parameter. Here's an example:

dao.paginate(1, 10, true, "select *", "from girl where age > ? group by age", 18);

In the above code, the outermost SQL contains a group by age, so the isGroupBySql parameter should be set to true.

For nested SQL where the group by isn't in the outermost layer, the parameter must be false, e.g., select * from (select x from t group by y) as temp.

Emphasizing once more: the isGroupBy parameter should be true only when the outermost SQL has a group by clause. If only the inner SQL in nested queries has a group by, then it should be false.

3. paginateByFullSql

API prototype: paginateByFullSql(int pageNumber, int pageSize, String totalRowSql, String findSql, Object... paras).

This separates the two SQLs used for getting the total row count and fetching the data. It's mainly used for handling situations where there are complex order by clauses or when select contains distinct. You should only use this API when the first paginate method throws exceptions. Here's an example:

String from = "from girl where age > ?";
String totalRowSql = "select count(*) " + from;
String findSql = "select * " + from + " order by age";
dao.paginateByFullSql(1, 10, totalRowSql, findSql, 18);

The key with paginateByFullSql is that both totalRowSql and findSql should be able to use the last parameter, Object... paras.

4. paginate with SqlPara Parameter

API prototype: paginate(int pageNumber, int pageSize, SqlPara sqlPara). This is meant to be used in conjunction with SQL management functionality, which will be discussed in the SQL management section.

5. Common Issues and Solutions

To effectively highlight and address issues, we first need to understand the underlying implementation principles of paginate.

The core principle is that JFinal will use the provided select and sqlExceptSelect strings to generate the SQL needed to compute the total rows that match the query conditions. The order by clause, if present, is removed since many databases don't support it in count(*) type queries and because it doesn't change the result.

Issues arise when:

1. The select part of the query contains placeholders. This can be solved by wrapping the original SQL in an outer query.
2. The order by clause contains subqueries or function calls. In this case, the solution is to use paginateByFullSql which lets users specify the total row count and data fetching SQLs separately.

In summary, understanding the inner workings of paginate makes troubleshooting straightforward. If all else fails, paginateByFullSql is the go-to solution, albeit at the cost of writing a bit more code.